package com.grasp.db;

import java.util.ArrayList;
import java.util.List;

import cc.wulian.ihome.wan.entity.DeviceEPInfo;

import com.grasp.base.Logger;
import com.grasp.data.DeviceItem;
import com.grasp.data.IconItem;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;

public class DeviceTable implements SQLiteInterface {

	private static final int COLUMN0 = 0;
	private static final int COLUMN1 = 1;
	private static final int COLUMN2 = 2;
	private static final int COLUMN3 = 3;
	private static final int COLUMN4 = 4;
	private static final int COLUMN5 = 5;
	private static final int COLUMN6 = 6;
	private static final int COLUMN7 = 7;
	private static final int COLUMN8 = 8;
	private static final int COLUMN9 = 9;
	private static final int COLUMN10 = 10;
	private static final int COLUMN11 = 11;
	private static final int COLUMN12 = 12;
	private static final int COLUMN13 = 13;
	private static final int COLUMN14 = 14;
	private static final int COLUMN15 = 15;
	private static final int COLUMN16 = 16;

	private MySQLiteHelper mMySQLiteHelper;
	private SQLiteDatabase mSQLiteDatabase;

	/*
	 * ============ 设备表 ============
	 * 
	 * private String devID;//设备ID(服务器) private String devRoomID; private String
	 * devName;//设备名称 private String devType; private String devCategory;
	 * private String devEP; private String devEpName; private String
	 * devEpStatus; private String devEpData; private String senceID; private
	 * String areaID; private String devTypeID;
	 */
	public static final String DEVICE_TABLE = "DeviceTypeTable";
	private static final String TABLE_ID = "_id";
	private static final String DEVICE_TITLE = "dev_title";
	private static final String DEVICE_ICON_NAME = "icon_name";// 图标名称

	private static final String DEV_ID = "device_id";// 设备ID
	private static final String DEV_ROOM_ID = "device_room_id";
	private static final String DEV_NAME = "device_name";
	private static final String DEV_TYPE = "device_type";
	private static final String DEV_CATEGORY = "device_category";

	private static final String DEVICE_EP = "dev_ep";// ep端口
	private static final String DEVICE_EP_PORT = "dev_ep_port";
	private static final String DEVICE_EP_NAME = "dev_ep_name";
	private static final String DEVICE_EP_STATE = "dev_ep_state";
	private static final String DEVICE_EP_DATA = "dev_ep_data";
	private static final String DEVICE_AREA_ID = "area_id";
	private static final String DEVICE_SENCE_ID = "sence_id";
	private static final String DEVICE_TYPE_ID = "device_type_id";
	private static final String DEVICE_DESC = "_describe";
	private static final String DEVICE_COLUMNS[] = { TABLE_ID, DEVICE_TITLE,
			DEVICE_ICON_NAME, DEV_ID, DEV_ROOM_ID, DEV_NAME, DEV_TYPE,
			DEV_CATEGORY, DEVICE_EP, DEVICE_EP_PORT, DEVICE_EP_NAME,
			DEVICE_EP_STATE, DEVICE_EP_DATA, DEVICE_AREA_ID, DEVICE_SENCE_ID,
			DEVICE_TYPE_ID, DEVICE_DESC };

	public DeviceTable(Context context) {
		mMySQLiteHelper = new MySQLiteHelper(context, this);
		mSQLiteDatabase = mMySQLiteHelper.getWritableDatabase();
	}

	@Override
	public void createTable(SQLiteDatabase db) {
		String deviceSql = "CREATE TABLE IF NOT EXISTS " + DEVICE_TABLE + " ("
				+ DEVICE_COLUMNS[COLUMN0]
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ DEVICE_COLUMNS[COLUMN1] + " VARCHAR, "
				+ DEVICE_COLUMNS[COLUMN2] + " VARCHAR, "
				+ DEVICE_COLUMNS[COLUMN3] + " VARCHAR, "
				+ DEVICE_COLUMNS[COLUMN4] + " VARCHAR, "
				+ DEVICE_COLUMNS[COLUMN5] + " VARCHAR, "
				+ DEVICE_COLUMNS[COLUMN6] + " VARCHAR, "
				+ DEVICE_COLUMNS[COLUMN7] + " VARCHAR, "
				+ DEVICE_COLUMNS[COLUMN8] + " VARCHAR, "
				+ DEVICE_COLUMNS[COLUMN9] + " VARCHAR, "
				+ DEVICE_COLUMNS[COLUMN10] + " VARCHAR, "
				+ DEVICE_COLUMNS[COLUMN11] + " VARCHAR, "
				+ DEVICE_COLUMNS[COLUMN12] + " VARCHAR, "
				+ DEVICE_COLUMNS[COLUMN13] + " INTEGER, "
				+ DEVICE_COLUMNS[COLUMN14] + " INTEGER, "
				+ DEVICE_COLUMNS[COLUMN15] + " INTEGER, "
				+ DEVICE_COLUMNS[COLUMN16] + " VARCHAR," + "FOREIGN KEY("
				+ DEVICE_COLUMNS[COLUMN13] + ") REFERENCES "
				+ AreaTable.AREA_TABLE + "(" + TABLE_ID + ")," + "FOREIGN KEY("
				+ DEVICE_COLUMNS[COLUMN14] + ") REFERENCES "
				+ SenceTable.SENCE_TABLE + "(" + TABLE_ID + "),"
				+ "FOREIGN KEY(" + DEVICE_COLUMNS[COLUMN15] + ") REFERENCES "
				+ DeviceTypeTable.DEVICE_TYPE_TABLE + "(" + TABLE_ID + "));";
		db.execSQL(deviceSql);
		Logger.d("===>deviceSql:" + deviceSql);
	}

	@Override
	public void onUpgrade(int oldVersion, int newVersion) {

	}

	/**
	 * private static final String DEVICE_TITLE = "dev_title"; private static
	 * final String DEVICE_ICON_NAME = "icon_name";// 图标名称
	 * 
	 * private static final String DEV_ID = "device_id";// 设备ID private static
	 * final String DEV_ROOM_ID = "device_room_id"; private static final String
	 * DEV_NAME = "device_name"; private static final String DEV_TYPE =
	 * "device_type"; private static final String DEV_CATEGORY =
	 * "device_category";
	 * 
	 * private static final String DEVICE_EP = "dev_ep";// ep端口 private static
	 * final String DEVICE_EP_PORT = "dev_ep_port"; private static final String
	 * DEVICE_EP_NAME = "dev_ep_name"; private static final String
	 * DEVICE_EP_STATE = "dev_ep_state"; private static final String
	 * DEVICE_EP_DATA = "dev_ep_data"; private static final String
	 * DEVICE_AREA_ID = "area_id"; private static final String DEVICE_SENCE_ID =
	 * "sence_id"; private static final String DEVICE_TYPE_ID =
	 * "device_type_id"; private static final String DEVICE_DESC = "_describe";
	 * 
	 * @param item
	 * @return
	 */
	public boolean insert(DeviceItem item) {
		ContentValues values = new ContentValues();
		values.put(DEVICE_COLUMNS[COLUMN1], item.getTitle());
		values.put(DEVICE_COLUMNS[COLUMN2], item.getFileName());
		values.put(DEVICE_COLUMNS[COLUMN3], item.getDevID());
		values.put(DEVICE_COLUMNS[COLUMN4], item.getDevRoomID());
		values.put(DEVICE_COLUMNS[COLUMN5], item.getDevName());
		values.put(DEVICE_COLUMNS[COLUMN6], item.getDevType());
		values.put(DEVICE_COLUMNS[COLUMN7], item.getDevCategory());
		values.put(DEVICE_COLUMNS[COLUMN8], item.getDevEP());
		values.put(DEVICE_COLUMNS[COLUMN9], item.getDevEpPort());
		values.put(DEVICE_COLUMNS[COLUMN10], item.getDevEpName());
		values.put(DEVICE_COLUMNS[COLUMN11], item.getDevEpStatus());
		values.put(DEVICE_COLUMNS[COLUMN12], item.getDevEpData());
		if (!TextUtils.isEmpty(item.getAreaID()))
			values.put(DEVICE_COLUMNS[COLUMN13], item.getAreaID());
		if (!TextUtils.isEmpty(item.getSenceID()))
			values.put(DEVICE_COLUMNS[COLUMN14], item.getSenceID());
		if (!TextUtils.isEmpty(item.getDevTypeID()))
			values.put(DEVICE_COLUMNS[COLUMN15], item.getDevTypeID());
		values.put(DEVICE_COLUMNS[COLUMN16], item.getDesc());
		long number = mSQLiteDatabase.insert(DEVICE_TABLE, null, values);
		Logger.i("===>insert:" + number);
		return number == 1 ? true : false;
	}

	public boolean delete(DeviceItem item) {
		String[] whereArgs = { String.valueOf(item.getId()) };
		int number = mSQLiteDatabase.delete(DEVICE_TABLE,
				DEVICE_COLUMNS[COLUMN0] + "=?", whereArgs);
		return number == 1 ? true : false;
	}

	public boolean update(DeviceItem item) {
		ContentValues values = new ContentValues();
		values.put(DEVICE_COLUMNS[COLUMN1], item.getTitle());
		values.put(DEVICE_COLUMNS[COLUMN2], item.getFileName());
		values.put(DEVICE_COLUMNS[COLUMN3], item.getDevID());
		values.put(DEVICE_COLUMNS[COLUMN4], item.getDevRoomID());
		values.put(DEVICE_COLUMNS[COLUMN5], item.getDevName());
		values.put(DEVICE_COLUMNS[COLUMN6], item.getDevType());
		values.put(DEVICE_COLUMNS[COLUMN7], item.getDevCategory());
		values.put(DEVICE_COLUMNS[COLUMN8], item.getDevEP());
		values.put(DEVICE_COLUMNS[COLUMN9], item.getDevEpPort());
		values.put(DEVICE_COLUMNS[COLUMN10], item.getDevEpName());
		values.put(DEVICE_COLUMNS[COLUMN11], item.getDevEpStatus());
		values.put(DEVICE_COLUMNS[COLUMN12], item.getDevEpData());
		if (!TextUtils.isEmpty(item.getAreaID()))
			values.put(DEVICE_COLUMNS[COLUMN13], item.getAreaID());
		if (!TextUtils.isEmpty(item.getSenceID()))
			values.put(DEVICE_COLUMNS[COLUMN14], item.getSenceID());
		if (!TextUtils.isEmpty(item.getDevTypeID()))
			values.put(DEVICE_COLUMNS[COLUMN15], item.getDevTypeID());
		values.put(DEVICE_COLUMNS[COLUMN16], item.getDesc());
		String[] whereArgs = { String.valueOf(item.getId()) };
		int number = mSQLiteDatabase.update(DEVICE_TABLE, values,
				DEVICE_COLUMNS[COLUMN0] + "=?", whereArgs);
		return number >= 1 ? true : false;
	}

	public List<DeviceItem> queryAll() {
		List<DeviceItem> items = new ArrayList<DeviceItem>();
		Cursor mCursor = mSQLiteDatabase.query(DEVICE_TABLE, DEVICE_COLUMNS,
				null, null, null, null, null);
		while (mCursor.moveToNext()) {
			DeviceItem item = new DeviceItem();
			item.setId(mCursor.getInt(COLUMN0));
			item.setTitle(mCursor.getString(COLUMN1));
			item.setFileName(mCursor.getString(COLUMN2));
			item.setDesc(mCursor.getString(COLUMN16));
			items.add(item);
		}
		return items;
	}

}
